###############################################################################
# Script to clean Ohio statewide audit data without making a big fuss about it
#
# written badly by sbaltz at mit, november 2022
###############################################################################
import pandas as pd

ohraw = pd.read_csv('../transcribed/audit-totals.csv',
                    low_memory=False, encoding='utf-8')
ohraw = ohraw.rename(columns={
    'County': 'county',
    'Audit  - Total Ballots Cast (Presidential Contest)': 'ballots',
    'Accuracy Percentage Rate (Presidential Contest)': 'issues'
})
oh = pd.DataFrame([ohraw.county, ohraw.ballots, ohraw.issues]).T
oh.issues = oh.issues.str.replace('%','', regex=True).astype(float)
oh.issues = (1 - oh.issues/100) * oh.ballots
oh["office"] = "US PRESIDENT"
ohraw = ohraw.drop(['ballots', 'issues'], axis=1)
ohraw = ohraw.rename(columns={
    'Audit - Total Ballots Cast (Supreme Court Contest)': 'ballots',
    'Accuracy Rate (Supreme Court Contest)': 'issues'
})
ohnew = pd.DataFrame([ohraw.county, ohraw.ballots, ohraw.issues]).T
ohnew.issues = ohnew.issues.str.replace('%','', regex=True).astype(float)
ohnew.issues = (1 - ohnew.issues/100) * ohnew.ballots
ohnew["office"] = "SUPREME COURT"
oh = pd.concat([oh, ohnew])
ohraw = ohraw.drop(['ballots', 'issues'], axis=1)
ohraw = ohraw.rename(columns={
    'Audit - Total Ballots Cast (Audited Contest #3)': 'ballots',
    'Accuracy Rate (Audited Contest #3)': 'issues'
})
ohnew = pd.DataFrame([ohraw.county, ohraw.ballots, ohraw.issues]).T
ohnew.issues = ohnew.issues.str.replace('%','', regex=True).astype(float)
ohnew.issues = (1 - ohnew.issues/100) * ohnew.ballots
ohnew["office"] = ohraw['Audited Contest #3'].str.upper()
oh = pd.concat([oh, ohnew])

oh['state'] = 'OHIO'
oh['method'] = 'MANUAL'
oh.county = oh.county.str.upper()

#Drop the two RLA counties
oh = oh[~(oh.county.isin(['CUYAHOGA','MONTGOMERY']))]
oh.ballots = oh.ballots.astype(int)

oh.to_csv('../ready/oh_statewide_cleaned.csv', encoding='utf-8', index=False)

